﻿using BasicComponents.Model;
using Microsoft.Win32;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace BasicComponents.com
{
    /// <summary>
    /// Window_ImportUtils.xaml 的交互逻辑
    /// </summary>
    public partial class Window_ImportXLSUtils : Window
    {
        public delegate void GetbackDataTable(DataTable dt);
        public event GetbackDataTable getBackDataTable;
        //转换后的待导入数据
        DataTable dt_afterGzTable = null;
        ObservableCollection<DygxClass> dygxList = new ObservableCollection<DygxClass>();
        public ObservableCollection<DygxClass> DygxList { get => dygxList; set => dygxList = value; }

        Dictionary<String, String> dbColumns = new Dictionary<string, string>();
        ObservableCollection<string> excel_import_col_list = new ObservableCollection<string>();//导入时，显示列集合
        private string excelpath = "";

        public Window_ImportXLSUtils(Dictionary<String, String> DBColumns)
        {
            InitializeComponent();
            this.DataContext = this;
            //初始化获取列信息
            foreach (String key in DBColumns.Keys)
            {
                String val;
                DBColumns.TryGetValue(key, out val);
                dbColumns.Add(key, val);
                this.DygxList.Add(new DygxClass { XlsColList = new ObservableCollection<string>(), DBFieldDM = key, DBFieldName = val });
            }
        }

        private void btn_openXls_Click(object sender, RoutedEventArgs e)
        {
            initOrUpdateDygx();
        }
        //设置与字段的对应关系xls
        private void initOrUpdateDygx()
        {
            string strFileName = null;
            try
            {
                //打开xls选择窗口
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx";
                ofd.ValidateNames = true;
                ofd.CheckPathExists = true;
                ofd.CheckFileExists = true;
                ofd.Multiselect = false;

                ofd.Title = "选择导入的电子表格";

                if (ofd.ShowDialog() == true)
                {
                    strFileName = ofd.FileName;
                    this.excelpath = strFileName;
                }
                if (!string.IsNullOrWhiteSpace(strFileName))
                {
                    IWorkbook workbook;
                    using (FileStream fs = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                    {
                        if (strFileName.IndexOf(".xls") > 0)
                        {
                            workbook = new HSSFWorkbook(fs);
                        }
                        else if (strFileName.IndexOf(".xlsx") > 0)
                        {
                            workbook = new XSSFWorkbook(fs);
                        }
                        else
                        {
                            workbook = null;
                        }
                        if (workbook == null)
                        {
                            MessageBox.Show("选择文件不是excel文件，请核对后选择！");
                            return;
                        }
                        //initeximportColumn();
                        ISheet sheet = workbook.GetSheetAt(0);
                        if (sheet != null)
                        {
                            // this.excelpath = strFileName;
                            IRow headerrow = sheet.GetRow(sheet.FirstRowNum);//获取表头数据
                            if (headerrow != null && headerrow.Cells != null)
                            {
                                //清空对应关系的xls列内容及下拉框内容
                                foreach (DygxClass dygx in this.DygxList)
                                {
                                    dygx.XlsColList.Clear();
                                    dygx.XlsColName = "";
                                }
                                for (int i = headerrow.FirstCellNum; i < headerrow.LastCellNum; i++)//遍历表头数据列将其保存到集合中
                                {
                                    string headercolvalue = headerrow.GetCell(i).ToString();
                                    excel_import_col_list.Add(headercolvalue);
                                    //循环添加xls列内容 
                                    foreach (DygxClass dygx in this.DygxList)
                                    {
                                        dygx.XlsColList.Add(headercolvalue);
                                        if (dygx.DBFieldName.Equals(headercolvalue))
                                        {
                                            dygx.XlsColName = headercolvalue;
                                        }
                                    }
                                }
                            }
                            else
                            {
                                MessageBox.Show("电子表格中无任何数据", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
                            }

                        }
                        else
                        {
                            MessageBox.Show("电子表格中无任何数据", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
                        }


                    }
                }

            }
            catch (Exception exc)
            {
                MessageBox.Show("导入时发生错误，错误信息：" + exc.Message, "错误", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }
        //预览导入
        private void initPreView()
        {
            try
            {
                // 清空
                this.dg_previewData.Columns.Clear();
                this.dt_afterGzTable = new DataTable();
                if (!string.IsNullOrWhiteSpace(this.excelpath))
                {
                    IWorkbook workbook;
                    using (FileStream fs = new FileStream(this.excelpath, FileMode.Open, FileAccess.Read))
                    {
                        if (excelpath.IndexOf(".xls") > 0)
                        {
                            workbook = new HSSFWorkbook(fs);
                        }
                        else if (excelpath.IndexOf(".xlsx") > 0)
                        {
                            workbook = new XSSFWorkbook(fs);
                        }
                        else
                        {
                            workbook = null;
                        }
                        if (workbook == null)
                        {
                            MessageBox.Show("未发现Excel数据，无法导入！", "提示");
                            return;
                        }
                        ISheet sheet = workbook.GetSheetAt(0);
                        if (sheet != null)
                        {
                            DataTable dt_xls = GetDataTableFromSheet(sheet, 0);

                            // 最终数据表格列信息
                            foreach (DygxClass gzxx in this.dygxList)
                            {
                                this.dt_afterGzTable.Columns.Add(gzxx.DBFieldDM);
                            }
                            // 根据xls表格及对应关系， 添加数据行
                            for (int i = 0; i < dt_xls.Rows.Count; i++)
                            {
                                DataRow row = dt_xls.Rows[i];
                                DataRow rowGz = this.dt_afterGzTable.NewRow();
                                this.dt_afterGzTable.Rows.Add(rowGz);
                                foreach (DygxClass gzxx in this.dygxList)
                                {
                                    String xlsColName = gzxx.XlsColName;
                                    String dbFieldDM = gzxx.DBFieldDM;
                                    String dbFieldName = gzxx.DBFieldName;
                                    if (xlsColName != null && !xlsColName.Equals("")) {
                                        rowGz[dbFieldDM] = row[xlsColName];
                                    }
                                }
                            }

                            /*
                            foreach (DygxClass gzxx in this.dygxList)
                            { 
                                String colName = gzxx.XlsColName;
                                Boolean hasCol = false;
                                for (int i = 0; i < dt_xls.Columns.Count; i++)
                                {
                                    DataColumn col = dt_xls.Columns[i];
                                    if (col.ColumnName.Equals(colName))
                                    {
                                        col.ColumnName = gzxx.DBFieldName;
                                        hasCol = true;
                                    }
                                }
                                if (!hasCol)
                                {
                                    this.dt_afterGzTable.Columns.Add(gzxx.DBFieldDM);
                                }
                            }
                            */
                            //列排序  
                            /*
                            foreach (DygxClass gzxx in this.dygxList)
                            {
                                for (int i = 0; i < this.dt_afterGzTable.Columns.Count; i++)
                                {
                                    DataColumn col = this.dt_afterGzTable.Columns[i];
                                    if (col.ColumnName.Equals(gzxx.DBFieldName) && DygxList.IndexOf(gzxx) >= 0)
                                    {
                                        col.SetOrdinal(DygxList.IndexOf(gzxx));
                                        break;
                                    }
                                }
                            }
                            */
                            if (this.dt_afterGzTable != null && this.dt_afterGzTable.Rows != null && this.dt_afterGzTable.Rows.Count > 0)
                            {
                                dg_previewData.ItemsSource = null;
                                //设置表头
                                initPreViewColHeader();
                                dg_previewData.ItemsSource = this.dt_afterGzTable.AsDataView();
                            }
                            else
                            {
                                MessageBox.Show("Excel没有导入的数据可预览！");
                            }
                        }
                        else
                        {
                            MessageBox.Show("没有选择Excel表");
                            return;
                        }
                    }
                }
                else
                {
                    MessageBox.Show("请“打开xls文件”后重试！", "提示");
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show("预览数据失败，错误信息:" + exc.Message, "错误", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        } //设置预览表头
        private void initPreViewColHeader()
        {
            this.dg_previewData.Items.Clear();
            foreach (DygxClass gzDygx in this.DygxList)
            {
                DataGridTextColumn col = new DataGridTextColumn();
                Style dgColHeaderStyle = (Style)this.FindResource("style_dgColHeader");
                // if (gzDygx.XlsColName == null || gzDygx.XlsColName.Trim().Equals("")) continue;//去除空列
                col.Header = gzDygx.DBFieldName;
                col.SortMemberPath = gzDygx.DBFieldName;
                col.IsReadOnly = false;
                col.Binding = new Binding(gzDygx.DBFieldDM);
                col.HeaderStyle = dgColHeaderStyle;
                dg_previewData.Columns.Add(col);
            }
        }
        //将xls的sheet的内容转换为dataTable。
        private DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
        {
            DataTable table = new DataTable();
            try
            {
                IRow headerRow = sheet.GetRow(headerRowIndex);
                int cellCount = headerRow.LastCellNum;
                //dg_previewData.select
                //foreach (DygxClass dygx in dygxList) { 
                //    DataColumn column = new DataColumn(dygx.DBFieldName); 
                //    table.Columns.Add(column);
                //}
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                    {
                        // 如果遇到第一个空列，则不再继续向后读取
                        cellCount = i + 1;
                        break;
                    }
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);

                    if (row != null && !string.IsNullOrEmpty(row.Cells[0].ToString()))
                    {
                        DataRow dataRow = table.NewRow();
                        //foreach (DygxClass dygx in this.DygxList) {
                        //    if (!String.IsNullOrEmpty(dygx.XlsColName)) {
                        //        row.GetCell()
                        //    }
                        //}
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                ICell cell = row.GetCell(j);
                                String cellVal = "";
                                try
                                {
                                    cellVal = cell.NumericCellValue.ToString();
                                }
                                catch (Exception e1)
                                {
                                    cellVal = cell.ToString();

                                }

                                dataRow[j] = cellVal;
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show("转换xls数据时发生错误，错误信息:" + exc.Message + exc.StackTrace, "错误", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            return table;
        }
        private void lookExcel_Click(object sender, RoutedEventArgs e)
        {
            initPreView();
        }

        private void importOk_Click(object sender, RoutedEventArgs e)
        {
            if (getBackDataTable != null)
            {
                if (this.dt_afterGzTable.Rows.Count > 0)
                {
                    getBackDataTable(this.dt_afterGzTable);
                    this.Close();
                }
                else
                {
                    MessageBox.Show("没有任何需要导入的数据，无法导入", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
                }
            }
            else
            {
                MessageBox.Show("无法导入,因为调用者没有实现该数据回调事件！", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
            }
        }

        private void dg_previewData_LoadingRow(object sender, DataGridRowEventArgs e)
        {
            e.Row.Header = e.Row.GetIndex() + 1;
        }

        private void Btn_exportXls_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                SaveFileDialog sdig = new SaveFileDialog();
                sdig.Filter = "电子报表2003|*.xls";
                if (sdig.ShowDialog().Value)
                {
                    DataTable dt = new DataTable();
                    foreach (string val in this.dbColumns.Values)
                    {
                        dt.Columns.Add(val);
                    }
                    String filename = sdig.FileName;
                    if (!filename.EndsWith(".xls") && !filename.EndsWith(".xlsx"))
                    {
                        filename += ".xls";
                    }
                    if (IOExcle.dataTableToExcel(dt, filename))
                    {
                        MessageBox.Show("导出成功，导出模板到文件" + filename + "成功!", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
                    }
                }
            }
            catch (Exception e1)
            {
                MessageBox.Show("导出模板失败，错误信息:" + e1.Message);
            }
        }

        private void Btn_priviewXls_Click(object sender, RoutedEventArgs e)
        {
            initPreView();
        }
    }
}
